package dk.javacode.srsm;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;

import dk.javacode.srsm.exceptions.MappingException;

/**
 * Creates an insert SQL statement from a given pojo. Note that POJOs that uses
 * custom converters will likely fail on insert - or worse may succeed in
 * inserting invalid data into the database. (A future solution may be to make
 * converters two-way)
 */
public class SqlBuilder {
	public SqlSelect buildSelectSql(Class<?> clazz) throws MappingException {
		TableDescriptor tableDescriptor = TableDescriptor.build(clazz);
		SqlSelect sql = new SqlSelect(tableDescriptor.getTableName());
		for (ColumnDescriptor cd : tableDescriptor.getColumnDescriptors()) {
			sql.addColumn(cd.getColumnName());
		}
		return sql;
	}

	/**
	 * Returns a result containing the Sql and the parameterValues as a list.
	 * This method uses setAccessible(true) on fields and therefore won't work
	 * in some security contexts.
	 * 
	 * @throws MappingException
	 */
	public <E> SqlInsert buildInsertSql(String table, E object) throws IllegalArgumentException,
			IllegalAccessException, MappingException {
		if (object == null) {
			throw new RuntimeException("Cannot insert null object");
		}
		SqlInsert result = new SqlInsert();
		String columns = "";
		String values = "";

		TableDescriptor tableDescriptor = TableDescriptor.build(object.getClass());
		for (ColumnDescriptor cd : tableDescriptor.getColumnDescriptors()) {
			Field f = cd.getColumnField();
			f.setAccessible(true);
			Object value = f.get(object);
			String columnName = cd.getColumnName();
			if (cd.getColumnReference() != null && value != null) {
				cd.getColumnReference().setAccessible(true);
				value = cd.getColumnReference().get(value);
			}
			if (value != null) {
				value = cd.getDataConverter().pojoToDatabase(value);
				columns += columnName + ", ";
				values += "?, ";
				result.addParameterValue(columnName, value);
			}
		}
		if (columns.length() == 0) {
			throw new RuntimeException("No fields found on object.");
		}
		columns = columns.substring(0, columns.length() - 2);
		values = values.substring(0, values.length() - 2);
		String sql = "insert into " + table + " (" + columns + ") values (" + values + ")";
		result.setSql(sql);
		return result;
	}

	/**
	 * Given an open connection this method attempts to insert 'object' into
	 * 'table'.
	 * 
	 * @param connection
	 * @param object
	 * @param table
	 * @throws IllegalArgumentException
	 * @throws IllegalAccessException
	 * @throws SQLException
	 * @throws MappingException
	 */
	public <E> Integer insert(Connection connection, E object, String table) throws IllegalArgumentException,
			IllegalAccessException, SQLException, MappingException {
		SqlInsert sql = buildInsertSql(table, object);
		PreparedStatement stmt = connection.prepareStatement(sql.getSql(), Statement.RETURN_GENERATED_KEYS);
		int i = 1;
		for (Object o : sql.getParameterValues().values()) {
			stmt.setObject(i, o);
			i++;
		}
		stmt.execute();
		ResultSet keys = stmt.getGeneratedKeys();
		if (!keys.next()) {
			// Not all tables generate keys..
			return null;
		}
		return keys.getInt(1);
	}

	public static class SqlSelect {
		private List<SqlColumn> columns = new ArrayList<SqlColumn>();
		private String table;
		private List<SqlJoin> joins = new ArrayList<SqlJoin>();

		public SqlSelect(String table) {
			super();
			this.table = table;
		}

		public void addJoin(SqlJoin join) {
			joins.add(join);
		}

		public void addJoin(Class<?> clazz, String joinColumn, String refColumn) throws MappingException {
			TableDescriptor td = TableDescriptor.build(clazz);
			SqlJoin join = new SqlJoin(td.getTableName(), joinColumn, refColumn);
			joins.add(join);
			for (ColumnDescriptor cd : td.getColumnDescriptors()) {
				if (!cd.getColumnName().equals(joinColumn)) {
					addColumn(td.getTableName(), cd.getColumnName());
				}
			}
		}

		public void addJoin(Class<?> clazz, String joinColumn, String refColumn, JoinType type) throws MappingException {
			TableDescriptor td = TableDescriptor.build(clazz);
			SqlJoin join = new SqlJoin(td.getTableName(), joinColumn, refColumn, type);
			joins.add(join);
			for (ColumnDescriptor cd : td.getColumnDescriptors()) {
				if (!cd.getColumnName().equals(joinColumn)) {
					addColumn(td.getTableName(), cd.getColumnName());
				}
			}
		}

		public void addColumn(String column) {
			columns.add(new SqlColumn(column));
		}

		public void addColumn(String table, String column) {
			columns.add(new SqlColumn(table, column, column));
		}

		public void addColumn(SqlColumn column) {
			columns.add(column);
		}

		public void removeColumn(String column) {
			columns.remove(column);
		}

		public String getSelect() {
			StringBuilder sb = new StringBuilder();
			sb.append("select ");
			int i = 0;
			for (SqlColumn c : columns) {
				i++;
				String lab = c.getLabel();
				String col = c.getColumn();
				if (c.getTable() != null) {
					if (!c.getTable().equals(table)) {
						lab = c.getTable() + "__" + lab;
					}
					col = c.getTable() + "." + col;
				} else {
					col = table + "." + col;
				}
				sb.append(col);
				if (!col.equals(lab)) {
					sb.append(" as " + lab);
				}
				if (i < columns.size()) {
					sb.append(", ");
				}

			}
			sb.append(" from " + table);
			for (SqlJoin j : joins) {
				sb.append(" " + j.getType().getValue() + " " + j.joinTable + " on ");
				sb.append(table + "." + j.refColumn);
				sb.append(" = " + j.joinTable + "." + j.joinColumn);
			}
			return sb.toString();
		}
	}

	public static class SqlColumn {
		private String column;
		private String label;
		private String table;

		public SqlColumn() {
			super();
		}

		public SqlColumn(String column) {
			super();
			this.column = column;
			this.label = column;
		}

		public SqlColumn(String column, String label) {
			super();
			this.column = column;
			this.label = label;
		}

		public SqlColumn(String table, String column, String label) {
			super();
			this.column = column;
			this.label = label;
			this.table = table;
		}

		public String getColumn() {
			return column;
		}

		public void setColumn(String column) {
			this.column = column;
		}

		public String getLabel() {
			return label;
		}

		public void setLabel(String label) {
			this.label = label;
		}

		public String getTable() {
			return table;
		}

		public void setTable(String table) {
			this.table = table;
		}

	}

	public static class SqlJoin {
		private String joinTable;
		private String joinColumn;
		private String refColumn;
		private JoinType type = JoinType.JOIN;

		public SqlJoin() {
			super();
		}

		public SqlJoin(String joinTable, String joinColumn, String refColumn) {
			super();
			this.joinTable = joinTable;
			this.joinColumn = joinColumn;
			this.refColumn = refColumn;
		}

		public SqlJoin(String joinTable, String joinColumn, String refColumn, JoinType type) {
			super();
			this.joinTable = joinTable;
			this.joinColumn = joinColumn;
			this.refColumn = refColumn;
			this.type = type;
		}

		public JoinType getType() {
			return type;
		}

		public void setType(JoinType type) {
			this.type = type;
		}

		public String getJoinTable() {
			return joinTable;
		}

		public void setJoinTable(String joinTable) {
			this.joinTable = joinTable;
		}

		public String getJoinColumn() {
			return joinColumn;
		}

		public void setJoinColumn(String joinColumn) {
			this.joinColumn = joinColumn;
		}

		public String getRefColumn() {
			return refColumn;
		}

		public void setRefColumn(String refColumn) {
			this.refColumn = refColumn;
		}

	}

	public static class SqlInsert {
		private String sql;
		private LinkedHashMap<String, Object> parameterValues = new LinkedHashMap<String, Object>();

		public SqlInsert() {
			super();
		}

		public SqlInsert(String sql, LinkedHashMap<String, Object> parameterValues) {
			super();
			this.sql = sql;
			this.parameterValues = parameterValues;
		}

		public String getSql() {
			return sql;
		}

		public void setSql(String sql) {
			this.sql = sql;
		}

		public LinkedHashMap<String, Object> getParameterValues() {
			return parameterValues;
		}

		public void addParameterValue(String name, Object value) {
			parameterValues.put(name, value);
		}
	}

	public static enum JoinType {
		JOIN("join"), LEFT_JOIN("left join"), RIGHT_JOIN("right join"), INNER_JOIN("inner join"), OUTER_JOIN(
				"outer join");

		private final String value;

		private JoinType(String value) {
			this.value = value;
		}

		public String getValue() {
			return value;
		}
	}
}
